﻿// HSS.Data.ModelGenerator.DatabaseExplorer.cs
// ----------------------------------------------------------------------------
// Licensed under the MIT license
// http://www.opensource.org/licenses/mit-license.html
// ----------------------------------------------------------------------------
// HighSpeed-Solutions, LLC
// Copyright (c) 2001-2010
// ----------------------------------------------------------------------------
// File:       DatabaseExplorer.cs
// Author:     HSS\gbanta
// Created:    08/12/2010
// Modified:   12/24/2010
// ----------------------------------------------------------------------------
namespace HSS.Data.ModelGenerator
{
	#region Using Directives
	using System;
	using System.Collections.Generic;
	using System.Collections.ObjectModel;
	using System.ComponentModel;
	using System.Data.SqlClient;
	using System.Windows.Forms;
	using HSS.Data;
	using HSS.Data.ModelGenerator;
	using HSS.Forms;

	#endregion

	#region DatabaseExplorer
	/// <summary>
	/// DatabaseExplorer UserControl enumerates the objects of a database
	/// as a hierarchy of nodes.
	/// </summary>
	public partial class DatabaseExplorer : UserControl
	{
		#region Events

		/// <summary>
		/// Occurs when an Tree Node is selected
		/// </summary>
		public event EventHandler<DatabaseObjectSelectedEventArgs> NodeSelected;
		/// <summary>
		/// Raises the <see cref="NodeSelected"/> event.
		/// </summary>
		/// <param name="selectedObject"></param>
		protected void OnNodeSelected(ObjectDefinition selectedObject)
		{
			var handler = NodeSelected;
			if (null != handler)
				handler(this.exvDatabaseExplorer, new DatabaseObjectSelectedEventArgs(selectedObject));
		}
		/// <summary>
		/// Occurs when the selected database changes
		/// </summary>
		public event EventHandler<EventArgs> DatabaseChanged;
		/// <summary>
		/// Raises the <see cref="DatabaseChanged"/> event.
		/// </summary>
		protected void OnDatabaseChanged()
		{
			var handler = DatabaseChanged;
			if (null != handler)
				handler(this.exvDatabaseExplorer, EventArgs.Empty);
		}
		#endregion

		#region Fields

		delegate void SetRootDelegate(RunWorkerCompletedEventArgs e);

		static readonly object locker = new object();
		BackgroundWorker worker;

		TreeNode root;
		TreeNode databases;
		string lastNodePath;

		List<string> dbNames = new List<string>();

		string sprocFolder = "Stored Procedures";
		string tableFolder = "Tables";
		string viewsFolder = "Views";
		string header = "Connection";

		bool dataBindComplete;

		int selectDBIndex = -1;

		#endregion

		#region Constructors
		/// <summary>
		/// Constructor
		/// </summary>
		public DatabaseExplorer()
		{
			InitializeComponent();

			#region Set Explorer Header
			this.lbgHeader.Text = header + " [  ]";
			#endregion

		}
		#endregion

		#region Properties
		/// <summary>
		/// Gets the currently seleted DB Object or NULL if not object is selected
		/// </summary>
		public ObjectDefinition CurrentObject
		{
			get;
			private set;
		}
		#endregion

		#region Methods

		/// <summary>
		/// Query the database, and bind the schema to explorer view
		/// </summary>
		public void Bind()
		{
			dataBindComplete = false;

			this.tscDatabases.Items.Clear();

			this.exvDatabaseExplorer.AfterCollapse -= new TreeViewEventHandler(exvDatabaseExplorer_AfterCollapse);
			this.exvDatabaseExplorer.AfterExpand -= new TreeViewEventHandler(exvDatabaseExplorer_AfterExpand);
			this.exvDatabaseExplorer.Nodes.Clear();

			// Set Explorer Header
			this.lbgHeader.Text = header + " [ " + DataAccessContext.DefaultConnection.Name + " ]";

			// Create Root Node
			this.root = new DatabaseObjectNode(DataAccessContext.DefaultConnection.DataSource + " [ SQL Server ]");
			this.root.ImageKey = "server";
			this.root.SelectedImageKey = "server";

			// Create Databases Container node
			this.databases = new DatabaseObjectNode("Databases");
			this.databases.ImageKey = "databases";
			this.databases.SelectedImageKey = "databases";
			this.root.Nodes.Add(databases);

			// Show busy...
			this.databases.Nodes.Add("Loading...");

			// Show intial folders
			this.exvDatabaseExplorer.Nodes.Clear();
			this.exvDatabaseExplorer.Nodes.Add(root);
			this.root.Expand();
			this.databases.Expand();

			// Begin Extracting Schema
			worker = new BackgroundWorker();
			worker.DoWork += new DoWorkEventHandler(worker_DoWork);
			worker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(worker_RunWorkerCompleted);
			worker.RunWorkerAsync();

		}
		/// <summary>
		/// Refresh the Schema for the specified Catalog
		/// </summary>
		public void RefreshSchema()
		{
			if (!dataBindComplete)
			{
				MsgBox.Show("Cannot refresh the schema until the Database Explorer has been bound to a connection.");
				return;
			}

			databases.Nodes.Clear();
			databases.Nodes.Add("Loading...");

			this.exvDatabaseExplorer.Update();
			this.exvDatabaseExplorer.Enabled = false;

			var dbNode = this.BindDatabase();

			databases.Nodes.Clear();
			databases.Nodes.Add(dbNode);
			databases.Expand();
			dbNode.Expand();

			this.exvDatabaseExplorer.Enabled = true;
			this.exvDatabaseExplorer.Focus();

			var selectedNode = this.FindFromPath(this.lastNodePath);
			this.exvDatabaseExplorer.SelectedNode = selectedNode ?? dbNode;
		}

		TreeNode BindAsnyc()
		{
			#region Get list of Databases
			this.dbNames.Clear();
			using (var da = new DataAccess(Program.MasterDBConnectionString))
			{
				using (var dr = da.ExecDataRowReaderSQL("SELECT name FROM master.dbo.sysdatabases WHERE HAS_DBACCESS(name) = 1 ORDER BY name"))
				{
					while (dr.Read())
					{
						string dbName = dr.GetString(dr.GetOrdinal("name"));
						dbNames.Add(dbName);
					}
				}
			}
			#endregion

			// Bind the Initial Catalog (heavy lifting is done here)
			TreeNode dbNode = null;
			if (dbNames.Count > 0)
				dbNode = this.BindDatabase();

			return dbNode;
		}
		TreeNode BindDatabase()
		{
			var defaultDB = DataAccessContext.DefaultConnection.InitialCatalog;
			if (string.IsNullOrEmpty(defaultDB))
			{
				defaultDB = dbNames[0];
				DataAccessContext.DefaultConnection.InitialCatalog = defaultDB;
			}
			var dbNode = new DatabaseObjectNode(defaultDB);
			dbNode.Name = dbNode.Text;
			dbNode.ImageKey = "database";
			dbNode.SelectedImageKey = "database";

			using (var da = new DataAccess(DataAccessContext.DefaultConnection))
			{
				#region Gets Tables
				var tables = new DatabaseObjectNode(tableFolder);
				tables.ImageKey = "closed";
				tables.SelectedImageKey = "closed";
				using (var dr = da.ExecSqlDataReaderSQL(Properties.Resources.GetTableSchema))
				{
					while (dr.Read())
					{
						var schema = dr.GetString(dr.GetOrdinal("schema"));
						var name = dr.GetString(dr.GetOrdinal("name"));
						var tblNode = new DatabaseObjectNode(name + " [" + schema + "]");
						tblNode.Tag = new ObjectDefinition() { ClassName = name, ObjectName = name, Schema = schema, IsTable = true };
						tblNode.ImageKey = "table";
						tblNode.SelectedImageKey = "selected";
						tables.Nodes.Add(tblNode);
					}
				}
				dbNode.Nodes.Add(tables);
				#endregion

				#region Gets Views
				var views = new DatabaseObjectNode(viewsFolder);
				views.ImageKey = "closed";
				views.SelectedImageKey = "closed";
				using (var dr = da.ExecSqlDataReaderSQL(Properties.Resources.GetViewSchema))
				{
					while (dr.Read())
					{
						var schema = dr.GetString(dr.GetOrdinal("schema"));
						var name = dr.GetString(dr.GetOrdinal("name"));
						var tblNode = new DatabaseObjectNode(name + " [" + schema + "]");
						tblNode.Tag = new ObjectDefinition() { ClassName = name, ObjectName = name, Schema = schema, IsTable = true };
						tblNode.ImageKey = "view";
						tblNode.SelectedImageKey = "selected";
						views.Nodes.Add(tblNode);
					}
				}
				dbNode.Nodes.Add(views);
				#endregion

				#region Gets Sprocs
				var sprocs = new DatabaseObjectNode(sprocFolder);
				sprocs.ImageKey = "closed";
				sprocs.SelectedImageKey = "closed";
				using (var dr = da.ExecSqlDataReaderSQL(Properties.Resources.GetSprocSchema))
				{
					while (dr.Read())
					{
						var schema = dr.GetString(dr.GetOrdinal("schema"));
						var name = dr.GetString(dr.GetOrdinal("name"));
						var sprocNode = new DatabaseObjectNode(name + " [" + schema + "]");
						sprocNode.Tag = new ObjectDefinition() { ClassName = name, ObjectName = name, Schema = schema, IsTable = false };
						sprocNode.ImageKey = "sproc";
						sprocNode.SelectedImageKey = "selected";
						sprocs.Nodes.Add(sprocNode);
					}
				}
				dbNode.Nodes.Add(sprocs);
				#endregion
			}

			return dbNode;
		}
		TreeNode FindFromPath(string nodePath)
		{
			if (string.IsNullOrEmpty(nodePath))
				return null;

			string[] tree = nodePath.Split(".".ToCharArray());
			TreeNode curNode = this.root;
			foreach (var child in tree)
			{
				if (child == curNode.Text)
					continue;

				TreeNode[] nodes = this.exvDatabaseExplorer.Nodes.Find(child, true);
				if (null != nodes && nodes.Length > 0)
					curNode = nodes[0];
				else
					return null;
			}

			return curNode;
		}

		static void BindObject(ObjectDefinition obj, TreeNode node)
		{
			if (null != obj)
			{
				if (obj.IsTable)
				{
					if (string.IsNullOrEmpty(obj.SqlDefinition))
						obj.SqlDefinition = "select * from " + obj.Schema + "." + obj.ObjectName;
				}
				else
				{
					#region Stored Procedures

					#region Sql Definition
					if (string.IsNullOrEmpty(obj.SqlDefinition))
					{
						using (var da = new DataAccess(DataAccessContext.DefaultConnection))
						{
							string sprocDefSql = "SELECT m.definition FROM sys.sql_modules AS m INNER JOIN sys.procedures AS p ON m.object_id = p.object_id WHERE (p.name = '" + obj.ObjectName + "')";
							obj.SqlDefinition = (string)da.ExecScalarSQL(sprocDefSql);
						}
					}
					#endregion

					#region Parameters
					if (null == obj.Parameters)
					{
						using (var da = new DataAccess(DataAccessContext.DefaultConnection))
							obj.Parameters = new ReadOnlyCollection<SqlParameter>(da.GetProcParameters(obj.Schema + "." + obj.ObjectName));
					}
					#endregion

					#endregion
				}
			}
		}
		void BindComplete(RunWorkerCompletedEventArgs e)
		{
			if (this.exvDatabaseExplorer.InvokeRequired)
			{
				this.exvDatabaseExplorer.Invoke(new SetRootDelegate(BindComplete), e);
				return;
			}

			databases.Nodes.Clear();

			if (null != e.Error)
			{
				MsgBox.Show(e.Error.Message);
				return;
			}

			var dbNode = e.Result as TreeNode;
			if (null == dbNode)
			{
				MsgBox.Show("Unable to retrieve any database for the current connection.");
				return;
			}

			#region List databases available to the user
			for (int i = 0; i < this.dbNames.Count; i++)
			{
				string dbName = this.dbNames[i];
				this.tscDatabases.Items.Add(dbName);
				if (dbName.Equals(DataAccessContext.DefaultConnection.InitialCatalog, StringComparison.OrdinalIgnoreCase))
					this.selectDBIndex = i;
			}
			#endregion

			#region Set currently selected database
			if (this.tscDatabases.Items.Count > 0)
			{
				if (this.selectDBIndex == -1)
					this.tscDatabases.SelectedIndex = 0;
				else
					this.tscDatabases.SelectedIndex = this.selectDBIndex;
			}
			#endregion

			#region Finish building Tree

			dbNode.Expand();
			databases.Nodes.Add(dbNode);
			databases.Expand();

			var selectedNode = this.FindFromPath(this.lastNodePath);
			if (null == selectedNode)
				selectedNode = this.exvDatabaseExplorer.Nodes[DataAccessContext.DefaultConnection.InitialCatalog];
			this.exvDatabaseExplorer.SelectedNode = selectedNode ?? dbNode;
			this.exvDatabaseExplorer.Focus();

			this.exvDatabaseExplorer.AfterCollapse += new TreeViewEventHandler(exvDatabaseExplorer_AfterCollapse);
			this.exvDatabaseExplorer.AfterExpand += new TreeViewEventHandler(exvDatabaseExplorer_AfterExpand);
			this.exvDatabaseExplorer.AfterSelect += new TreeViewEventHandler(exvDatabaseExplorer_AfterSelect);

			#endregion

			dataBindComplete = true;

		}
		void ExpandAll()
		{
			if (null != this.exvDatabaseExplorer.SelectedNode)
				this.exvDatabaseExplorer.SelectedNode.ExpandAll();
		}
		void CollapseAll()
		{
			if (null != this.exvDatabaseExplorer.SelectedNode)
				this.exvDatabaseExplorer.SelectedNode.Collapse(false);
		}

		#endregion

		#region Event Handlers

		void worker_DoWork(object sender, DoWorkEventArgs e)
		{
			e.Result = this.BindAsnyc();
		}
		void worker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
		{
			this.BindComplete(e);
		}

		void exvDatabaseExplorer_AfterExpand(object sender, TreeViewEventArgs e)
		{
			lock (locker)
			{
				if (e.Action == TreeViewAction.Expand &&
				(e.Node.Text == tableFolder || e.Node.Text == viewsFolder || e.Node.Text == sprocFolder))
				{
					e.Node.ImageKey = "open";
					e.Node.SelectedImageKey = "open";
				}

				ObjectDefinition obj = e.Node.Tag as ObjectDefinition;
				BindObject(obj, e.Node);
			}
		}
		void exvDatabaseExplorer_AfterCollapse(object sender, TreeViewEventArgs e)
		{
			if (e.Action == TreeViewAction.Collapse &&
				(e.Node.Text == tableFolder || e.Node.Text == viewsFolder || e.Node.Text == sprocFolder))
			{
				e.Node.ImageKey = "closed";
				e.Node.SelectedImageKey = "closed";
			}
		}
		void exvDatabaseExplorer_AfterSelect(object sender, TreeViewEventArgs e)
		{
			lock (locker)
			{
				lastNodePath = e.Node.FullPath;
				this.CurrentObject = e.Node.Tag as ObjectDefinition;
				BindObject(this.CurrentObject, e.Node);
				this.OnNodeSelected(this.CurrentObject);
			}
		}

		void tsbCollapse_Click(object sender, EventArgs e)
		{
			this.CollapseAll();
		}
		void tsbExpand_Click(object sender, EventArgs e)
		{
			this.ExpandAll();
		}

		void tscDatabases_DropDownClosed(object sender, EventArgs e)
		{
			if (this.selectDBIndex != this.tscDatabases.SelectedIndex)
			{
				this.selectDBIndex = this.tscDatabases.SelectedIndex;
				DataAccessContext.DefaultConnection.InitialCatalog = (string)this.tscDatabases.SelectedItem;
				this.RefreshSchema();
				this.OnDatabaseChanged();
			}
		}
		#endregion
	}
	#endregion
}